Data Viz Makeover 1

This is my first attempt at redesigning a graph according to the design principles taught in class.

Sean Samuel Prajs https://github.com/SeanP0110 (SMU)
2022/02/13

1 Critique

Prof. Kam has given us the following graph to work on:

Original Graph

1.1 Clarity

First we analyse the clarity of the graph and the elements (or lack thereof) that impede clarity.

  1. Title
  1. Axes
  1. Trends

1.2 Aestethics

  1. Sorting
  1. Ink
  1. Axes

2 Improvements

2.1 A first sketch

Below you can see the plan for a new and improved visualisation: GraphPlan

2.2 Clarity

  1. Title
  1. Axes
  1. Trends

2.3 Aestethics

  1. Sorting
  1. Ink
  1. Axes

3 Visualisation

The following is the final visualisation:

FinalGraph The link to Tableau Public is here.

4 Step-By-Step Guide to Visualisation

Step Change Screenshot
1. Duplicate Excel File and rename to: “Resident Labour Force Participation Rate by Age and Sex Clean.xls” Image
2. Create 3 extra tabs in the Excel File named “Total,” “Male,” and “Female.” Image
3. Copy and paste and the header row from “mrsd_Res_LFPR_2” into each one of the new sheet with the first cell always being A3 Image
4. Copy and paste the respective table to each of the 3 new sheets. Important: paste values only. Save and close the file. Image
5. Change the “Males” and “Females” values in cell A4 of the “Male” and “Female worksheets into “Total” Image
6. Repeat Steps 2-5 using the data from “mrsd_Res_LFPR_1”. This time call the three new sheets “Total Summary,” “Male Summary,” and “Female Summary” Image
7. Open a new Tableau document and import “Resident Labour Force Participation Rate by Age and Sex Clean.xls” Image
8. Drag “Total,” “Male,” “Female,” “Total Summary,” “Male Summary,” and “Female Summary” in as a data source. The relation between tables should be: Age (Years) / Sex = Age (Years) / Sex. Image
9. Hide column “2007A” (“2007a”) in each table. Image
10. For each of the tables select each year column. This means every column from 1991-2021.Right click on any column while holding the selection and click “Pivot.” Image
11. Rename the “Pivot Field Names” column into “Year (Table Name).” Image
12. Change the Year column data type from “String” to “Date” Image
13. Change the “Pivot Field Values” column data type from “String” to “Number (decimal)” Image
14. Create a calculated column by dividing the values in the “Pivot Field Values” column by 100. Name the new column “LFPR (Table Name)” for each table. Hide the “Pivot Field Values” column. Image
15. In each of the relations between the tables, equate the year columns. Image
16. For each table, rename “Age (Years) / Sex” to “Age Group (Table Name).” Image
17. Go to “Sheet 1” and rename it to “Age Group LFPR Comparison” Image
18. In each of the tables on the left hand pane select the respective LFPR column and select “Default Properties”  “Number Format.” Image
19. Change the number format to “Percentage” and click “Ok.” Keep 2 decimal places. Image
20. Collapse all tables except for “Total” Image
21. Add the Year component of “Year” to the column pane and “LFPR” to the row pane. Add “Age Group” to the Detail and Color panes. Image
22. From the above step we can see that the data for three years is missing. These years are 1995, 2000, and 2005. However, given that we are interested in the labour force participation rate over time rather than in specific years, we remove these null values from the graph. Click the box labelled “45 nulls” at the bottom right of the page. Image
23. Next, click filter data. This will exclude the three years without data from the graph. Image
24. Given that we have excluded certain values, we have to put in a warning such that any viewer of the graph will now this. Click on the little arrow at the top right of the title box and click “Edit Title” Image
25. Press enter and put in a place holder for the subtitle. Press enter two more times and enter the warning message as shown in the picture. The font should be 8, red, and boldened. Image
26. Exclude the categories “70 & Over” and “Total” from the graph. Image
27. Click the small arrow in the Age Group Legend pane. Click “Edit Colors.” Image
28. Click on the drop-down menu under “Select Color Palette” and choose “Hue Circle.” Image
29. “Assign Palette” and click “Ok.” Image
30. Hover over the y axis , right click, and select “Edit Axis.” Image
31. Go to the “Tick Marks” tab and for Major Tick Marks set “Fixed.” Also set Tick Origin = 0 and Tick Interval = 0.2. Image
32. To make the graph static, we need to turn off the tooltip. Go to Worksheet –> Tooltip Image
33. Uncheck “Show tooltips” and click “Ok.” Image
34. Hover over the blue Year ribbon in the column pane. Click on the little arrow that appears on the right-hand side. Select continuous. Image
35. We can see that the ticks on the x axis automatically change. However, there is a problem, the three missing years are included as ticks. Therefore, a change in tick values is needed. Right click the x axis and select “Edit Axis.” Image
36. Go to the “Tick Marks” tab and for Major Tick Marks set “Fixed.” Also set Tick Origin = 1 and Tick Interval = 5. Select “No” for Minor Tick Marks. Image
37. Go back to “General” and change the axis title to “Year” Image
38. Repeat the previous step for the y axis, renaming it “Labour Force Participation Rate.” Image
39. Right click the x axis and select “Add Reference Line.” Image
40. Add a reference line with following settings:
Scope:
• Per Pane
Line:
• Value: 2020 - Constant
• Label: Custom - Covid 19 Pandemic
• Tooltip: None
Formatting:
• Line: - - (middle selection)
Image
41. Add a reference line with following settings:
Scope:
• Per Pane
Line:
• Value: 1999 - Constant
• Label: Custom - SG Retirement increased to 62
• Tooltip: None
Formatting:
• Line: - - (middle selection)

Information Source for this can be found here.
Image
42. Add a third reference line with following settings:
Scope:
• Per Pane
Line:
• Value: 2008 - Constant
• Label: Custom - Subprime Mortgage Crisis
• Tooltip: None
Formatting:
• Line: - - (middle selection)
Image
43. Add a third reference line with following settings:
Scope:
• Per Pane
Line:
• Value: 1997 - Constant
• Label: Custom - Asian Financial Crisis
• Tooltip: None
Formatting:
• Line: - - (middle selection)
Image
44. Right click the Asian Financial Crisis reference line and select “Format…” Image
45. Change the alignment to left and up. Repeat this for the other three reference lines with the following allignments:
SG Retirement: right and up
Subprime Mortgage Crisis: left and down
Covid 19 Pandemic: left and down
Image
46. Last, change the title to following text:
(font 15)
Singapore’s Labour Participation is increasing in almost all age groups despite global crises.
(font 10)
Labour Force Participation Rate in Singapore across age groups between 1991 and 2021
Image

5 Key Insights

Finally, what can we learn from the graph?

  1. Crises do not seem to affect Singapore’s LFPR too much. Neither the Asian Financial Crisis in 1997 nor the Subprime Mortgage Crisis in 2008 have impacted the LFPR. All age groups just continue towards the trend they were following before the crisis.
  1. Singapore’s LFPR is increasing for most age groups. Other than the two youngest age groups, not a single one has had a long lasting downwards trend in the past 30 years. This is a positive development for Singapore’s workforce as a trend like this usually entails lower unemployment and poverty within the population.

  2. The age groups in the middle have the highest LFPR. Since 2008 the age groups between 25 and 59 have consistently had higher LFPR than the groups younger and older than them. Specifically, this gap has grown to more than 10% at its narrowest point in 2021.

  3. Singapore’s raise in retirement age has an affect on the “60 to 64” age group. However, there was a delay in policy effect. The implementation happened in 1999, while the first steep rise in this age group only happened around 2003. These policies usually are expected to have a delay.

  4. People age 20 to 24 have a significantly lower LFPR than they had 30 years ago. This might have to do with an increase in Singapore’s wealth which gave more young people the opportunity to attend tertiary education instead of having to work immediately.

  5. The gap between the top middle aged (25-59) age group and the bottom one has closed to a large extent. While in 1991 this gap seems to have been around 40%, it is only approximately 15% in 2021.

Distill Disclaimer

Distill is a publication format for scientific and technical writing, native to the web.

Learn more about using Distill for R Markdown at https://rstudio.github.io/distill.